package com.memory.dao.impl;

import com.memory.dao.UserDao;
import com.memory.pojo.User;
import com.memory.util.BaseDao;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: UserDaoImpl
 * Package: com.memory.dao.impl
 * Description:
 *
 * @Author Memory
 * @Create 2024/8/14 14:32
 * @Version 1.0
 */

public class UserDaoImpl implements UserDao {

    //已完成
    @Override
    public List<User> selectAll() throws SQLException {
        Connection connection = BaseDao.getConnection();
        Statement stat = null;
        ArrayList<User> userList = new ArrayList<>();

        try {
            stat = connection.createStatement();

            String sql = "SELECT * FROM students";
            ResultSet rs = stat.executeQuery(sql);
            if(rs==null){
                throw new IllegalArgumentException("students is null");
            }

            while (rs.next()) {
                User user = new User();
                user.setSno(rs.getInt("Sno"));
                user.setSname(rs.getString("Sname"));
                user.setSsex(rs.getString("Ssex"));
                user.setSage(rs.getInt("Sage"));
                user.setSdept(rs.getString("Sdept"));
                userList.add(user);
            }

            return userList;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            stat.close();
            connection.close();
        }
    }

    //已完成
    @Override
    public ArrayList<User> selectById(User user) throws SQLException {
        Connection connection = BaseDao.getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        ArrayList<User> userList = new ArrayList<>();

        try {
            String sql = "SELECT * FROM students where Sno = ? or Sname = ? or Ssex = ? or Sage = ? or Sdept = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, user.getSno());
            ps.setString(2, user.getSname());
            ps.setString(3, user.getSsex());
            ps.setInt(4, user.getSage());
            ps.setString(5, user.getSdept());
            rs = ps.executeQuery();
            System.out.println(rs);
            if(rs==null){
                throw new IllegalArgumentException("students is not exist");
            }
            while (rs.next()){
                User one = new User();
                one.setSno(rs.getInt("Sno"));
                one.setSname(rs.getString("Sname"));
                one.setSsex(rs.getString("Ssex"));
                one.setSage(rs.getInt("Sage"));
                one.setSdept(rs.getString("Sdept"));

                userList.add(one);
            }

            return userList;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                ps.close();
            }
            connection.close();
        }

    }


    public User selectBySno(int sno) throws SQLException {
        Connection connection = BaseDao.getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        User user = new User();

        try {
            String sql = "SELECT * FROM students where Sno = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, sno);
            rs = ps.executeQuery();
            if(rs==null){
                throw new IllegalArgumentException("students is not exist");
            }
            while (rs.next()){
                user.setSno(rs.getInt("Sno"));
                user.setSname(rs.getString("Sname"));
                user.setSsex(rs.getString("Ssex"));
                user.setSage(rs.getInt("Sage"));
                user.setSdept(rs.getString("Sdept"));
            }
            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                ps.close();
            }
            connection.close();
        }

    }
    //已完成
    @Transactional
    @Override
    public void update(User user) throws SQLException {
        Connection connection = BaseDao.getConnection();
        Statement stat = null;
        PreparedStatement ps = null;

        try {
            stat = connection.createStatement();
            String sql = "update students set Sname=?, Ssex=? , Sage=? , Sdept=? where Sno = ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, (user.getSname()));
            ps.setString(2, (user.getSsex()));
            ps.setInt(3, (user.getSage()));
            ps.setString(4, (user.getSdept()));
            ps.setInt(5, user.getSno());
            int row = ps.executeUpdate();
            System.out.println(row);
            if (row==0) {
                throw new IllegalArgumentException("students is not exist");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            stat.close();
            connection.close();
        }
    }


    //已完成
    @Override
    @Transactional
    public void insert(User user) throws SQLException {
        Connection connection = BaseDao.getConnection();
        Statement stat = null;
        ResultSet rs = null;

        PreparedStatement ps = null;
        try {
            stat = connection.createStatement();

            String sql = "insert into students values (?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, user.getSno());
            ps.setString(2, user.getSname());
            ps.setString(3, user.getSsex());
            ps.setInt(4, (user.getSage()));
            ps.setString(5, user.getSdept());
            int row = ps.executeUpdate();
            System.out.println(row);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            stat.close();
            connection.close();
        }


    }

    //已完成
    @Override
    @Transactional
    public void delete(int id) throws SQLException {
        Connection connection = BaseDao.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "delete from students where Sno = ?";
            ps = connection.prepareStatement(sql);

            ps.setInt(1, id);
            int row = ps.executeUpdate();
            System.out.println(row);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) {
                ps.close();
            }
            connection.close();
        }

    }


}
